<!DOCTYPE HTML>
<html lang="zh-CN">


<head>
    <meta charset="utf-8">
    <meta name="keywords" content="数据库实验报告, 十二惊惶的gitee">
    <meta name="description" content="[TOC]

这个主要是将本学期，的实验报告进行一个汇总

实验二：数据库的创建和使用操作一、实验目的
1.安装配置MySQL workbench或者sqlyog客户端，并实现服务的连接。
2.学习mysql workbench 的操作。
">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="referrer" content="no-referrer-when-downgrade">
    <!-- Global site tag (gtag.js) - Google Analytics -->


    <title>数据库实验报告 | 十二惊惶的gitee</title>
    <link rel="icon" type="image/png" href="/shier_jinghuang/favicon.png">
    


    <!-- bg-cover style     -->



<link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/awesome/css/all.min.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/materialize/materialize.min.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/aos/aos.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/animate/animate.min.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/lightGallery/css/lightgallery.min.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/css/matery.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/css/my.css">
<link rel="stylesheet" type="text/css" href="/shier_jinghuang/css/dark.css" media="none" onload="if(media!='all')media='all'">




    <link rel="stylesheet" href="/shier_jinghuang/libs/tocbot/tocbot.css">
    <link rel="stylesheet" href="/shier_jinghuang/css/post.css">




    



    <script src="/shier_jinghuang/libs/jquery/jquery-3.6.0.min.js"></script>

<meta name="generator" content="Hexo 5.4.0"><link rel="alternate" href="/shier_jinghuang/atom.xml" title="十二惊惶的gitee" type="application/atom+xml">
</head>


<body>
    <header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="nav-wrapper container">
            <div class="brand-logo">
                <a href="/shier_jinghuang/" class="waves-effect waves-light">
                    
                    <img src="/shier_jinghuang/medias/1.jpg" class="logo-img" alt="LOGO">
                    
                    <span class="logo-span">十二惊惶的gitee</span>
                </a>
            </div>
            

<a href="#" data-target="mobile-nav" class="sidenav-trigger button-collapse"><i class="fas fa-bars"></i></a>
<ul class="right nav-menu">
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/" class="waves-effect waves-light">
      
      <i class="fas fa-home" style="zoom: 0.6;"></i>
      
      <span>首页</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/tags" class="waves-effect waves-light">
      
      <i class="fas fa-tags" style="zoom: 0.6;"></i>
      
      <span>标签</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/categories" class="waves-effect waves-light">
      
      <i class="fas fa-bookmark" style="zoom: 0.6;"></i>
      
      <span>分类</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/archives" class="waves-effect waves-light">
      
      <i class="fas fa-archive" style="zoom: 0.6;"></i>
      
      <span>归档</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/about" class="waves-effect waves-light">
      
      <i class="fas fa-user-circle" style="zoom: 0.6;"></i>
      
      <span>关于</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/contact" class="waves-effect waves-light">
      
      <i class="fas fa-comments" style="zoom: 0.6;"></i>
      
      <span>留言板</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/shier_jinghuang/friends" class="waves-effect waves-light">
      
      <i class="fas fa-address-book" style="zoom: 0.6;"></i>
      
      <span>友情链接</span>
    </a>
    
  </li>
  
  <li>
    <a href="#searchModal" class="modal-trigger waves-effect waves-light">
      <i id="searchIcon" class="fas fa-search" title="搜索" style="zoom: 0.85;"></i>
    </a>
  </li>
  <li>
    <a href="javascript:;" class="waves-effect waves-light" onclick="switchNightMode()" title="深色/浅色模式" >
      <i id="sum-moon-icon" class="fas fa-sun" style="zoom: 0.85;"></i>
    </a>
  </li>
</ul>


<div id="mobile-nav" class="side-nav sidenav">

    <div class="mobile-head bg-color">
        
        <img src="/shier_jinghuang/medias/1.jpg" class="logo-img circle responsive-img">
        
        <div class="logo-name">十二惊惶的gitee</div>
        <div class="logo-desc">
            
            犁牛之子骍且角，虽欲勿用，山川其舍诸
            
        </div>
    </div>

    <ul class="menu-list mobile-menu-list">
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-home"></i>
			
			首页
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/tags" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-tags"></i>
			
			标签
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/categories" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-bookmark"></i>
			
			分类
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/archives" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-archive"></i>
			
			归档
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/about" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-user-circle"></i>
			
			关于
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/contact" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-comments"></i>
			
			留言板
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/shier_jinghuang/friends" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-address-book"></i>
			
			友情链接
		</a>
          
        </li>
        
        
        <li><div class="divider"></div></li>
        <li>
            <a href="https://gitee.com/Im-so-scared-2/shier_jinghuang" class="waves-effect waves-light" target="_blank">
                <i class="fab fa-github-square fa-fw"></i>十二惊惶
            </a>
        </li>
        
    </ul>
</div>


        </div>

        
            <style>
    .nav-transparent .github-corner {
        display: none !important;
    }

    .github-corner {
        position: absolute;
        z-index: 10;
        top: 0;
        right: 0;
        border: 0;
        transform: scale(1.1);
    }

    .github-corner svg {
        color: #0f9d58;
        fill: #fff;
        height: 64px;
        width: 64px;
    }

    .github-corner:hover .octo-arm {
        animation: a 0.56s ease-in-out;
    }

    .github-corner .octo-arm {
        animation: none;
    }

    @keyframes a {
        0%,
        to {
            transform: rotate(0);
        }
        20%,
        60% {
            transform: rotate(-25deg);
        }
        40%,
        80% {
            transform: rotate(10deg);
        }
    }
</style>

<a href="https://gitee.com/Im-so-scared-2/shier_jinghuang" class="github-corner tooltipped hide-on-med-and-down" target="_blank"
   data-tooltip="十二惊惶" data-position="left" data-delay="50">
    <svg viewBox="0 0 250 250" aria-hidden="true">
        <path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path>
        <path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2"
              fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path>
        <path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z"
              fill="currentColor" class="octo-body"></path>
    </svg>
</a>
        
    </nav>

</header>

    



<div class="bg-cover pd-header post-cover" style="background-image: url('/shier_jinghuang/medias/featureimages/16.jpg')">
    <div class="container" style="right: 0px;left: 0px;">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <h1 class="description center-align post-title">数据库实验报告</h1>
                </div>
            </div>
        </div>
    </div>
</div>




<main class="post-container content">

    
    <div class="row">
    <div id="main-content" class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            <div class="row tag-cate">
                <div class="col s7">
                    
                    <div class="article-tag">
                        
                            <a href="/shier_jinghuang/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">
                                <span class="chip bg-color">数据库</span>
                            </a>
                        
                    </div>
                    
                </div>
                <div class="col s5 right-align">
                    
                    <div class="post-cate">
                        <i class="fas fa-bookmark fa-fw icon-category"></i>
                        
                            <a href="/shier_jinghuang/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" class="post-category">
                                学习笔记
                            </a>
                        
                    </div>
                    
                </div>
            </div>

            <div class="post-info">
                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-minus fa-fw"></i>发布日期:&nbsp;&nbsp;
                    2022-07-06
                </div>
                

                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-check fa-fw"></i>更新日期:&nbsp;&nbsp;
                    2022-07-06
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-file-word fa-fw"></i>文章字数:&nbsp;&nbsp;
                    10.7k
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-clock fa-fw"></i>阅读时长:&nbsp;&nbsp;
                    45 分
                </div>
                

                
                    <div id="busuanzi_container_page_pv" class="info-break-policy">
                        <i class="far fa-eye fa-fw"></i>阅读次数:&nbsp;&nbsp;
                        <span id="busuanzi_value_page_pv"></span>
                    </div>
				
            </div>
        </div>
        <hr class="clearfix">

        
        <!-- 是否加载使用自带的 prismjs. -->
        <link rel="stylesheet" href="/shier_jinghuang/libs/prism/prism.min.css">
        

        

        <div class="card-content article-card-content">
            <div id="articleContent">
                <p>[TOC]</p>
<blockquote>
<p>这个主要是将本学期，的实验报告进行一个汇总</p>
</blockquote>
<h3 id="实验二：数据库的创建和使用操作"><a href="#实验二：数据库的创建和使用操作" class="headerlink" title="实验二：数据库的创建和使用操作"></a>实验二：数据库的创建和使用操作</h3><p><strong>一、实验目的</strong></p>
<p>1.安装配置MySQL workbench或者sqlyog客户端，并实现服务的连接。</p>
<p>2.学习mysql workbench 的操作。</p>
<p>3.使用mysql workbench创建school表格，并熟悉对简单数据的录入。</p>
<p><strong>二、实验要求</strong></p>
<p>1.成功运行workbench。</p>
<p>2.创建表格并实现对数据的录入。</p>
<p><strong>三、实验过程</strong></p>
<p>1.运行workbench。</p>
<p>新建一个数据库：在左侧的界面中点击schemas，在空白处右键，创建一个新的数据库。</p>
<img src="file:///C:\Users\888888\AppData\Local\Temp\ksohtml8672\wps1.jpg" style="zoom:80%;" />

<p>可以命名为school。新建表，点击apply，即为成功创建一个新的表。在左侧菜单内可以找到自己新建的表。打开表，可以在表格中输入自己想要的数据。</p>
<p>其中，数据库名，表名，表头名均不允许用中文。数据库中表格数据的大写选项解释如下：</p>
<blockquote>
<p>NN：not null 非空<br>UQ：unique 唯一索引 不许重复<br>BIN：binary 二进制数据(比text更大的二进制数据)<br>UN：unsigned 无符号   整数（非负数）<br>ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001<br>AI：auto increment 自增<br>G：generated column 生成列</p>
</blockquote>
<h3 id="实验三：数据库常见错误的试错与分析"><a href="#实验三：数据库常见错误的试错与分析" class="headerlink" title="实验三：数据库常见错误的试错与分析"></a>实验三：数据库常见错误的试错与分析</h3><p><strong>一、【实验目的】</strong></p>
<p>1.学会建立简单的数据库表格，并对常见出现的错误进行试错与分析。</p>
<p>2.学会限制数据的取值范围。</p>
<p><strong>二、【实验要求】</strong></p>
<p>1.进行对表格数据的试错。</p>
<p>2.认识在试错时候出现的错误。（数据类型错误，key键的是否唯一，数据的取值是否在规定的范围内等）</p>
<p>3.学会用外部键处理数据。</p>
<p><strong>三、【实验过程】</strong></p>
<p>一、数据类型错误，key不是唯一，或数据超过规定的长度：都是常见的数据库错误。</p>
<p>二. 限制数据的范围：</p>
<p>用外部键进行数据取值的限制，在sqlyog中实现这一操作。关系外键一般的取名原则为：fk（代表外键）_引用列名_被引用列名。</p>
<h3 id="实验四：数据库的导入和导出"><a href="#实验四：数据库的导入和导出" class="headerlink" title="实验四：数据库的导入和导出"></a>实验四：数据库的导入和导出</h3><p><strong>一、实验目的</strong></p>
<ol>
<li><p>掌握数据库的导入和导出相关知识</p>
</li>
<li><p>练习并使用cheak约束完成实验</p>
</li>
</ol>
<p><strong>二、实验要求</strong></p>
<p>(1) 使用MySQL创建数据库表</p>
<p>(2) 简单使用Data Export,Data import 练习导入和导出数据库</p>
<p>(3) 尝试使用sqlyog对数据库进行cheak约束。</p>
<p><strong>三、实验过程</strong></p>
<ol>
<li><p>建立数据库：创建具有三张表格的school数据库</p>
</li>
<li><p>找到Administration选项卡下的Data Export选项，选中创建好的school数据库，依次使用Export to Dump Project Folder和Export to self-Contained File验证实验结果，在使用Export to self-Contained File时生成一个.sql文件，在使用Export to Dump Project Folder生成一个文件夹。</p>
</li>
<li><p>使用Data import练习导入数据库</p>
<ul>
<li>选中数据库drop，删库。</li>
<li>Data import 分别选中两种导入方式导入数据库，刷新发现数据库被成功导入，名字仍然叫做school。</li>
<li>我们可以看到在导入和导入后的数据库信息不回发生改变。</li>
</ul>
</li>
<li><p>在SQLyog里，创建一张新表可以对其添加约束条件，我们可以使用sql语句使得，表格中部分选项数据设置一定的范围，在后续的使用中，如果写入数据不满足约束的话，系统会报错而不会执行，这是因为在添加约束时的约束项限制了我们可以导入的数据范围。</p>
</li>
<li><p>另外的，在不同版本的sqlyog中对这一操作的兼容不同，存在不少的Mysql版本并不支持执行约束。</p>
<ul>
<li>比如MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告，但是MySQL 8.0中可以使用check约束了。</li>
</ul>
</li>
</ol>
<p><strong>四、实验反思:</strong></p>
<p>在实验的过程中，我使用的sqlyog版本并不支持，check约束，我无法添加check约束，在解决这个问题的过程中，我查找这些问题，在他人的博客上，说check约束，在MySQL中只是作为一般的注释，对数据库没有约束功能。但是在借用同学计算机使用后，发现check语句能够对MySQL数据库进行约束，在通过了查阅很多资料后。</p>
<p>确认了在不同版本的sqlyog中对这一操作的兼容不同，存在不少的Mysql版本并不支持执行约束。</p>
<p>比如MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告，但是MySQL 8.0中可以使用check约束了。</p>
<h3 id="实验五：使用SQL语句创建和使用数据库"><a href="#实验五：使用SQL语句创建和使用数据库" class="headerlink" title="实验五：使用SQL语句创建和使用数据库"></a>实验五：使用SQL语句创建和使用数据库</h3><p><strong>一、实验目的</strong></p>
<ol>
<li>掌握SQL语句实现数据库的创建和使用</li>
</ol>
<p><strong>二、实验要求</strong></p>
<ol>
<li>使用MySQL创建数据库表</li>
<li>简单使用Data Export,Data import 练习导入和导出数据库</li>
<li>尝试使用sqlyog对数据库进行cheak约束。</li>
</ol>
<p><strong>三、实验过程</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">DROP DATABASE &#96;school&#96; ;
-- 删除原有数据库 school
CREATE SCHEMA &#96;school&#96; 
DEFAULT CHARACTER SET utf8 
COLLATE utf8_bin ;
-- 创建新数据库 school
use school;
-- 选择使用school数据库，避免修改默认数据库sys
CREATE TABLE student (
 sno CHAR(10) NOT NULL,
 cno VARCHAR(45) NOT NULL,
 sage INT NULL DEFAULT 18,
 PRIMARY KEY (sno))
COMMENT &#x3D; &#39;这是一个学生数据库&#39;;
-- 创建第一张student表
create table course(
cno char(4) not null primary key,
cname varchar(45) not null,
cpno char(4) references cno,
credit tinyint
);
-- 创建第二张course表
create table sc(
sno char(10),
cno char(4),
score int,
PRIMARY key(sno,cno),
foreign key(sno) references student(sno),
foreign key(sno) references course(cno)
);
-- 创建第三张sc表并设置两个外键<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p><strong>四、拓展学习：</strong></p>
<p><strong>MySQL 外键(FOREIGN KEY)用法：</strong></p>
<p>表与表的记录之间存在着三种关系：一对多、多对多、一对一的关系。处理表之间关系问题就会利用到FOREIGN KEY</p>
<p>约束1：在创建表时，先建被关联的表dep，才能建关联表emp</p>
<p>约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp</p>
<p>约束3：更新与删除都需要考虑到关联与被关联的关系。</p>
<p><strong>2.MySQL中支持的编码方式：</strong></p>
<p>整形：</p>
<table>
<thead>
<tr>
<th>tinyint(m)</th>
<th>1个字节表示(-128~127)</th>
</tr>
</thead>
<tbody><tr>
<td>smallint(m)</td>
<td>2个字节表示(-32768~32767)</td>
</tr>
<tr>
<td>mediumint(m)</td>
<td>3个字节表示(-8388608~8388607)</td>
</tr>
<tr>
<td>int(m)</td>
<td>4个字节表示(-2147483648~2147483647)</td>
</tr>
<tr>
<td>bigint(m)</td>
<td>8个字节表示(+-9.22*10的18次方)</td>
</tr>
</tbody></table>
<p>浮点型：</p>
<table>
<thead>
<tr>
<th>float(m,d)</th>
<th>单精度浮点型，8位精度(4字节),m是十进制数字的总个数， d是小数点后面的数字个数。</th>
</tr>
</thead>
<tbody><tr>
<td>double(m,d)</td>
<td>双精度浮点型，16位精度(8字节)</td>
</tr>
</tbody></table>
<p>字符型:</p>
<table>
<thead>
<tr>
<th>char(n)</th>
<th>固定长度的字符串，最多255个字符</th>
</tr>
</thead>
<tbody><tr>
<td>varchar(n)</td>
<td>固定长度的字符串，最多65535个字符</td>
</tr>
<tr>
<td>tinytext</td>
<td>可变长度字符串，最多255个字符</td>
</tr>
<tr>
<td>text</td>
<td>可变长度字符串，最多65535个字符</td>
</tr>
<tr>
<td>mediumtext</td>
<td>可变长度字符串，最多2的24次方-1个字符</td>
</tr>
<tr>
<td>longtext</td>
<td>可变长度字符串，最多2的32次方-1个字符</td>
</tr>
</tbody></table>
<p><strong>char和varchar：</strong></p>
<p>1.均可以经过指定n，来限制存储的最大字符数长度，char(20)和varchar(20)将最多只能存储20个字符，超过的字符将会被截掉。n必须小于该类型容许的最大字符数。</p>
<p>2.char类型指定了n以后，若是存入的字符数小于n，后面将会以空格补齐，查询的时候再将末尾的空格去掉，因此char类型存储的字符串末尾不能有空格，varchar不受此限制。</p>
<p>\3. 内部存储的机制不一样。char是固定长度，char(4)无论是存一个字符，2个字符或者4个字符（英文的），都将占用4个字节，varchar是存入的 实际字符数+1个字节（n&lt;=255）或2个字节(n&gt;255)，因此varchar(4),存入一个字符将占用2个字节，2个字符占用3个 字节，4个字符占用5个字节。</p>
<p>4.char类型的字符串检索速度要比varchar类型的快。</p>
<p><strong>varchar和text：</strong></p>
<p>1.都是可变长度的，最多能存储65535个字符。</p>
<p>2.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n&lt;=255）或2个字节(n&gt;255)，text是实际字符数+2个字节。</p>
<p>3.text类型不能有默认值。</p>
<p>4.varchar可直接建立索引，text建立索引要指定前多少个字符。查询速度varchar要快于text,在都建立了索引的状况下，text的索引好像没起做用，参见这篇文章：<a target="_blank" rel="noopener" href="http://www.javashuo.com/link?url=http://forums.mysql.com/read.php?24,105964,105964">http://forums.mysql.com/read.php?24,105964,105964</a></p>
<p>日期时间类型(date,time,datetime,timestamp)</p>
<table>
<thead>
<tr>
<th>date</th>
<th>日期’2008-12-2’</th>
</tr>
</thead>
<tbody><tr>
<td>time</td>
<td>时间’12:25:36’</td>
</tr>
<tr>
<td>datetime</td>
<td>日期时间’2008-12-2 22:06:44’</td>
</tr>
<tr>
<td>timestamp</td>
<td>不固定</td>
</tr>
</tbody></table>
<ol>
<li>timestamp比较特殊，若是定义一个字段的类型为timestamp，这个字段的时间会在其余字段修改的时候自动刷新。因此这个数据类型的字段能够存放这条记录最后被修改的时间，而不是真正来的存放时间。</li>
</ol>
<p>二进制数据(xxxBlob)：</p>
<ol>
<li>XXXBLOB和xxxtext是对应的，不过存储方式不一样，xxxTEXT是以文本方式存储的，若是存储英文的话区分大小写，而xxxBlob是以二进制方式存储的，不区分大小写。</li>
<li>xxxBlob存储的数据只能总体读出。</li>
<li>xxxTEXT能够指定字符集，xxxblob不用指定字符集。</li>
</ol>
<p>定点数(decimal)：</p>
<ol>
<li><p>decimal(m,d)  定点类型,浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为0<del>65，d小数点右侧数字的个数，范围为0</del>30，但不得超过m。</p>
</li>
<li><p>对定点数的计算能精确到65位数字。</p>
</li>
</ol>
<p>除此之外还有，<strong>空间数据类型</strong>如: <code>Geometry</code> :可以存储所有的几何类型，<code>Point</code>:简单点，<code>LINESTRING</code>  : 简单线，<code>POLYGON</code>:简单面，<code>MULTIPOINT</code> ：多点，<code>MULITILINESTRING</code>: 多线，<code>MUILITIPOLYGON</code>： 很多方面，<code>GEOMETRYCOLLECTION</code>:任何几何集合</p>
<h3 id="实验六：MySQL语句实现完整性约束"><a href="#实验六：MySQL语句实现完整性约束" class="headerlink" title="实验六：MySQL语句实现完整性约束"></a>实验六：MySQL语句实现完整性约束</h3><p><strong>一、实验目的</strong></p>
<p>1.使用mysql语句实现完整性约束。</p>
<p><strong>二、实验要求</strong></p>
<p>1.学习了解MySQL的各种约束</p>
<p>2.学会添加删除约束。</p>
<p><strong>三、实验过程</strong></p>
<p>默认值=缺省值=default value</p>
<p>id int default 1</p>
<p>使用列级约束：把约束的内容定义在列声明之后</p>
<p>表级约束：把约束定义放在所有列声明之后。</p>
<p>约束种类：</p>
<p>按照约束定义的种类来划分：</p>
<p>表级和列级约束。两者声明的方式不同，在所有列级约束声明完以后，在最后一行声明表级约束。</p>
<p>按照约束的内容来划分：</p>
<blockquote>
<p>default: 默认约束, 域完整性<br>not null: 非空约束，域完整性<br>unique: 唯一约束，实体完整性<br>primary key: 主键约束，实体完整性<br>foreign key: 外键约束，参照完整性<br>check: 检查约束（MySQL不支持），域完整性<br>auto_increment: 自增长约束<br>unsigned: 无符号约束<br>zerofill: 零填充约束</p>
</blockquote>
<p>按照作用的范围来划分：</p>
<blockquote>
<p>域完整性（对数据表中字段属性的约束）<br>实体完整性（通过主键约束和候选键位数来实现的）<br>参照完整性（不仅管理自己表的范围，而且涉及到跟别的表的范围，范围很大，MySQL的外键）</p>
</blockquote>
<p>复合约束：两个值不能同时一样。a引用b先创建b，先创建被引用的表。<br>添加外键约束：</p>
<img src="C:\Users\888888\AppData\Roaming\Typora\typora-user-images\1657064735337.png" alt="1657064735337" style="zoom:80%;" />

<p>删除约束：</p>
<img src="C:\Users\888888\AppData\Roaming\Typora\typora-user-images\1657064768883.png" style="zoom:80%;" />

<h3 id="实验七：SQL语句-数据查询"><a href="#实验七：SQL语句-数据查询" class="headerlink" title="实验七：SQL语句-数据查询"></a>实验七：SQL语句-数据查询</h3><p><strong>一、实验目的</strong></p>
<p>掌握利用sql语句进行数据的查询操作</p>
<p><strong>二、实验要求</strong></p>
<ol>
<li>熟练掌握SELECT 语句来查询数据，实现数据的查询操作</li>
<li>熟练掌握单表查询、分组查询、连接查询、嵌套查询和集合查询的查询方式</li>
<li>记录实验中遇到的问题和心得，写出实验报告。</li>
</ol>
<p><strong>三、实验过程</strong></p>
<blockquote>
<p>#SQL：结构化的查询语言<br>#交互式SQL，嵌入式SQL<br>#select 目标表达式列表<br>#from 目标关系列表<br>#where 目标条件列表 -&gt; 条件子句<br>#order by 排序子句<br>#group by 分组子句</p>
</blockquote>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">use school;
select * from student;
select sno,sname from student;
#重命名
select sno as &#39;学号&#39;, sname as &#39;姓名&#39; from student;
#as和单引号可省略，单引号使用防止字符有空格
select sno 学号,sname as &#39;姓  名&#39; from student;select 2022-sage &#39;出生年份&#39; from student;
#不加&#39;出生年份&#39;以&#39;2022-sage&#39;作为列名
select 2022-sage  from student;select cno from course;
#可跟函数
select lower(cno) from course;select upper(cno) from course;
#聚集函数&#x2F;统计查询
select count(*) from student;select count(*) as &#39;班级人数&#39; from student;
#单条件查询where
select sno,sname,ssex from student where ssex&#x3D;&#39;女&#39;;select sno,sname,ssex,sage from student where sage&lt;&#x3D;30 and ssex&#x3D;&#39;男&#39;;
select sno,sname,ssex,sage from student where sage&lt;&#x3D;30 or ssex&#x3D;&#39;男&#39;;
#&#39;男&#39;不要加空格,虽然有些软件可以自动去两边空格,仅侥幸
select * from student where sdept&#x3D;&#39;计算机系&#39; or sdept&#x3D;&#39;历史系&#39;;
#减短查询：查询集合
inselect * from student where sdept in (&#39;计算机系&#39;,&#39;音乐系&#39;,&#39;历史系&#39;);select * from student where sage&gt;&#x3D;23 and sage&lt;25;
#[23,25)
select * from student where not sage&gt;&#x3D;23 and sage&lt;25;select * from student where sage between 23 and 25;
#并，闭区间[23,25]，多条件无法实现 
#一个for语句遍历所有的行，where为真true才被选中
#数据库中第三种状态：除了真假，还有未知
select * from course;select * from course where not cpno&#x3D;&#39;C001&#39;; 
#为空未被选中
select * from course where cpno!&#x3D;&#39;C001&#39;; 
select * from course where cpno&#x3D;null;
#无法比较……得到未知
select * from course where cpno is null;
select * from course where cpno is not null;
#方式一select * from course where not (cpno is null);
#方式二#只限制前三条查询：起始下标0,偏移量3（网页分页查询使用）
select * from student where ssex&#x3D;&#39;男&#39; limit 0,3;
#所有的人按系科排序（默认：升序排序asc）-&gt; 字符集utf8 排序规则
utf8_binselect * from student order by sdept desc;
select * from student order by sdept desc, sage asc;
#放最后执行mysql<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="实验八：分组查询与模糊查询"><a href="#实验八：分组查询与模糊查询" class="headerlink" title="实验八：分组查询与模糊查询"></a>实验八：分组查询与模糊查询</h3><p><strong>一、实验目的</strong></p>
<ol>
<li>使用sql语句实现分组查询与模糊查询</li>
</ol>
<p><strong>二、实验要求</strong></p>
<ol>
<li><p>学会使用group by having条件分组查询</p>
</li>
<li><p>学会使用like模糊查询语句</p>
</li>
<li><p>学会使用% _通配符与escape的使用</p>
</li>
</ol>
<p><strong>三、实验过程</strong></p>
<ol>
<li><p>Mysql like %通配符有% 下划线_和escape三种</p>
</li>
<li><p>Mysql like区分大小写：匹配字符时，默认是不区分大小写，如果需要取区分大小写，则需要加入binary操作符，严格区分大小写，按照二进制进行比较</p>
</li>
<li><p>当like匹配时加上binary操作符后，则会严格区分英文大小写，因此当检索的内容是中英文混合，则许哟啊忽略英文大小写，使用UPPER(),LOWER()或CONCAT()函数:UPPER()函数将引文字符串变成大写，CONCAT()：将多个字符串连接成一个字符串</p>
</li>
<li><p>如果要找出既含有又含有但是不区分顺序的时候可以使用and条件</p>
</li>
<li><p>escape通配符：当既需要查%又需要查_时，不能作为通配符使用的时候，使用escape，转义字符后面的%或_就不作为通配符，注意钱买你没有转义字符的%和_仍然可以起到通配符的作用</p>
</li>
</ol>
<p><strong>实验代码：</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">use school;
select * from student order by sdept asc,sage desc;
select * from course where cpno&#x3D;null;
#null和任何数字比较得到的结果都是unknown。
select * from course where cpno is null;
select upper(cno),cname from course;
#查阅mysql手册查看各种功能函数
#统计查询
select count(*) from student 
group by sdept having 最小年龄&gt;&#x3D;20;
#having是对分组进行筛选alter，having必须在group by后面。可以使用别名进行筛选，但是应该用变量名
#如果重命名后的变量名有空可以用一对&#96;&#96;包括
#模糊查询
select * from student where sname like &#39;刘%&#39;;
select * from course where cname like &#39;C__程序设计%&#39;;
select * from course where cname like &#39;C\_ _程序设计%&#39;;
#\的作业是用来转义的，可以使用escape，规定转义字符，如果在查询内容中有\,就应该使用两个\\进行转义
select * from course where cname like  &#39;C#__程序设计%&#39;
escape &quot;#&quot;;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="实验九：多表查询的链接查询"><a href="#实验九：多表查询的链接查询" class="headerlink" title="实验九：多表查询的链接查询"></a>实验九：多表查询的链接查询</h3><p><strong>一、实验目的</strong></p>
<p><strong>1.多表查询的连接查询</strong></p>
<p><strong>2.多表查询的嵌套查询:不相关子查询</strong></p>
<p><strong>二、实验要求</strong></p>
<p>学习多表查询的用法，并自主拓展学习</p>
<p><strong>三、实验过程</strong> </p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">#一、单表查询：单表查询，条件查询， 
#3.is null，is not null，
#4.between and ， 
#5.count(*),min(*)
#6.目标表达式：重命名，列名、运算、函数;
#7.分组查询 group by  与having子句
#8.模糊查询  like escape &quot;&quot;
#9.排序 order by someone asc&#x2F;desc
#select 目标表达式列表
#from 目标关系列表
#where 目标条件列表
#group by 分组列列表
#order by 排序列列表<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<blockquote>
<p>#多表查询<br>#1.连接查询<br>#最简单链接：笛卡尔积(交叉连接)cross join<br>#内连接inner join…on:一般连接，等值连接，自然连接(公共属性上的等值连接)nature join<br>#外连接：左外连接left join…on，右外连接right join…on，全外连接full join<br>#2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询)<br>#不相关子查询(干完就跑路)in(…)<br>#一个值 ‘=’；多个值 ‘in’或’=ANY’<br>#sname&lt;&gt;”刘备”:*不包括刘备<br>#”&gt; ALL” == &gt; max 大于所有的<br>#”&gt; ANY” == &gt;min 大于某一个 P92</p>
</blockquote>
<p><strong>#3.集合查询</strong></p>
<p><strong>待续</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">use school;
#单表查询 9&#39;：
#简单查询、条件查询、空值查询(is null&#x2F;is not null)、
#between and、count(*) min(*)，目标表达式(列名、重名、运算、函数)、
#分组查询(group by...having)、模糊查询(like，escape，&quot;\\&quot;)、排序字句(order by...asc&#x2F;desc)
#select 目标表达式列表 from 目标关系列表 where 目标条件列表(and,or,not分割) group by 分组列 order by 排序依据列+排序方式
select * from sc;
select sno from sc where cno&#x3D;&quot;C003&quot;;
#找出选修C001也选修C003的同学学号
select sno,cno from sc where cno&#x3D;&quot;C001&quot; and cno&#x3D;&quot;C003&quot;;#错的
(select sno,cno from sc where cno&#x3D;&quot;C001&quot;) union (select sno,cno from sc where cno&#x3D;&quot;C003&quot;);
select sno from sc where cno&#x3D;&quot;C001&quot; and 
sno in (select sno from sc where cno&#x3D;&quot;C003&quot;);
select sc.sno,sname,sc.cno,cname,grade from student,course,sc where student.sno&#x3D;sc.sno and sc.cno&#x3D;course.cno and sc.cno&#x3D;&quot;C003&quot;;
#多表查询
#1.连接查询
select * from student,sc where student.sno&#x3D;sc.sno;
select sno,sname from student,sc where student.sno&#x3D;sc.sno and cno&#x3D;&quot;C003&quot;;#Error Code: 1052. Column &#39;sno&#39; in field list is ambiguous
select student.sno,sname from student,sc where student.sno&#x3D;sc.sno and cno&#x3D;&quot;C003&quot;;
select sc.sno,sname from (student inner join sc on student.sno&#x3D;sc.sno) where cno&#x3D;&quot;C003&quot;;
#最简单链接：笛卡尔积(交叉连接)cross join
select * from student,sc;
select * from student cross join sc;
#内连接inner join...on:一般连接，等值连接，自然连接(公共属性上的等值连接)nature join,
select * from student inner join sc;
select sc.sno,sname from (student inner join sc on student.sno&#x3D;sc.sno) where cno&#x3D;&quot;C003&quot;;
select * from student natural join sc;#只剩一行sno
select * from student natural join student;#Error Code: 1066. Not unique table&#x2F;alias: &#39;student&#39;
select * from student A natural join student B;#自己与自己自然连接，先相等再去掉一份

#外连接：左外连接left join...on，右外连接right join...on，全外连接full join
select * from student left join sc on student.sno&#x3D;sc.sno;
select * from sc right join student on student.sno&#x3D;sc.sno;
select * from student full join sc on student.sno&#x3D;sc.sno;#Error Code: 1054. Unknown column &#39;student.sno&#39; in &#39;on clause&#39;
select * from student full join sc ;
#2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询)
#不相关子查询(干完就跑路)
select sno,sname from student where
sno in (select cno from sc where cno&#x3D;&quot;C003&quot;);
select sname from student where sdept&#x3D;(select sdept from student where sname&#x3D;&quot;刘备&quot;);#一个值 ‘&#x3D;’；多个值 ‘in&#39;或&#39;&#x3D;ANY&#39;
select sname from student where sname&lt;&gt;&quot;刘备&quot; and sdept&#x3D;(select sdept from student where sname&#x3D;&quot;刘备&quot;);#sname&lt;&gt;&quot;刘备&quot; 不包括刘备
select * from student where sdept&lt;&gt;&quot;计算机系&quot; and sage&gt;ANY (select sage from student where sdept&#x3D;&quot;计算机系&quot;);
#&quot;&gt; ALL&quot; &#x3D;&#x3D; &gt; max 大于所有的
#&quot;&gt; ANY&quot; &#x3D;&#x3D; &gt;min 大于某一个 P92
select * from student where sage&gt;&#x3D;(select max(sage) from student where sdept &#x3D;&#39;计算机系&#39;);
#等价于
select * from student where sage&gt;&#x3D;all(select sage from student where sdept &#x3D;&#39;计算机系&#39;);
select * from student where sage&lt;any(select sage from student where sdept &#x3D;&#39;计算机系&#39;);
#小于any等价于小于最大值
#3.集合查询
(select sno,cno from sc where cno&#x3D;&quot;C001&quot;) union (select sno,cno from sc where cno&#x3D;&quot;C003&quot;);<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="实验十：相关子查询"><a href="#实验十：相关子查询" class="headerlink" title="实验十：相关子查询"></a>实验十：相关子查询</h3><p><strong>一、实验目的</strong></p>
<p><strong>1.多表查询的嵌套查询:相关子查询</strong></p>
<p><strong>二、实验要求</strong></p>
<p><strong>学习多表查询的用法，并自主拓展学习</strong></p>
<p><strong>三、实验过程</strong></p>
<p><strong>#嵌套查询</strong></p>
<p><strong>#1.不相关子查询</strong></p>
<p><strong>#2.相关子查询(本节课任务)</strong></p>
<p><strong>相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询)，相关子查询的时间复杂度远大于不相关子查询。</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">select sname from student where sname&#x3D;&quot;刘备&quot;;
select * from student where sdept&#x3D;(select sdept from student where sname&#x3D;&quot;刘备&quot;) and sname !&#x3D;&quot;刘备&quot;;
select * from student a ,student b where a.sname&#x3D;&quot;刘备&quot; and b.sname&lt;&gt;&quot;刘备&quot; and a.sdept&#x3D;b.sdept;
select b.sname from student a ,student b where a.sname&#x3D;&quot;刘备&quot; and b.sname&lt;&gt;&quot;刘备&quot; and a.sdept&#x3D;b.sdept;

select * from student X where sname&lt;&gt;&quot;刘备&quot; and exists(select * from student Y where sname &#x3D;&quot;刘备&quot; and Y.sdept&#x3D;X.sdept);
-- 以这一查询语句为例使用相关子查询的时间复杂度为n2+n(可以认为为n2的时间复杂度)，而实现相同工能的不相关子查询子需要n的时间复杂度
#EXISTS() 存在谓词,存在为真,反之为假
select sname as &quot;姓名&quot; from student where exists (select * from sc where sno&#x3D;student.sno and cno &#x3D;&quot;C003&quot;);
#查询选修了全部课程的学生的信息
select * from student where not exists(
select * from course where not exists(
select * from sc where student.sno &#x3D;sc.sno and sc.cno&#x3D;course.cno)
);
#查询选修了全部学分为2的课程的学生的信息
select * from student where not exists(
select * from course where credit &#x3D;2 and not exists(
select * from sc where student.sno &#x3D;sc.sno and sc.cno&#x3D;course.cno)
);
#查询选修了学号为202006001选修的全部课程的学生的信息
select sno from sc X where not exists(
select * from sc Y where Y.sno&#x3D;&quot;202006001&quot; and not exists(
se	lect * from sc Z where X.sno &#x3D;Z.sno and Y.cno&#x3D;Z.cno)
);
#关系代数在除法的运算在SQL语句中没有对应运算，通过存在谓词表示<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="实验十一：使用MySQL插入，删除，更新语句对数据库进行操作"><a href="#实验十一：使用MySQL插入，删除，更新语句对数据库进行操作" class="headerlink" title="实验十一：使用MySQL插入，删除，更新语句对数据库进行操作"></a>实验十一：使用MySQL插入，删除，更新语句对数据库进行操作</h3><p><strong>一、实验目的</strong></p>
<ol>
<li>了解和学会插入删除与更新语句:</li>
</ol>
<p><strong>二、实验要求</strong></p>
<ol>
<li>正确使用语句，查找实验中的问题并解决</li>
</ol>
<p><strong>三、实验过程</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">INSERT INTO &#96;school&#96;.&#96;student&#96; (&#96;sno&#96;, &#96;sname&#96;, &#96;ssex&#96;, &#96;sage&#96;, &#96;sdept&#96;) VALUES (&#39;202006018&#39;, &#39;司马懿&#39;, &#39;男&#39;, &#39;22&#39;, &#39;数学系&#39;)<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>第一次执行该程序，该条数据被正确的传入数据库，而在已经插入一次数据之后再次执行会显示</p>
<blockquote>
<p>Error Code: 1062. Duplicate entry ‘202006018’ for key ‘student.PRIMARY’</p>
</blockquote>
<p>这里的显示数据其实是，因为主码约束的结果，主码发生冲突。</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">DELETE FROM &#96;school&#96;.&#96;student&#96; WHERE (&#96;sno&#96; &#x3D; &#39;202006018&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>将刚刚输入的数据进行删除，第一次执行删除语句，没有出现问题，继续执行该语句删除操作的返回时间发生改变，返回时间为0，但是删除语句成功执行完成</p>
<p>在SC表中插入司马懿的关联数据</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">INSERT INTO &#96;school&#96;.&#96;sc&#96; (&#96;sno&#96;, &#96;cno&#96;, &#96;grade&#96;) VALUES (&#39;202006018&#39;, &#39;C007&#39;, &#39;90&#39;),(&#39;202006018&#39;, &#39;C006&#39;, &#39;86&#39;),(&#39;202006018&#39;, &#39;C005&#39;, &#39;89&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>再次执行删除语句</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">反馈为Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (&#96;school&#96;.&#96;sc&#96;, CONSTRAINT &#96;fk_sno&#96; FOREIGN KEY (&#96;sno&#96;) REFERENCES &#96;student&#96; (&#96;sno&#96;))<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>因为有外键约束的存在，sc表中的数据sno依赖于student表中的sno，在这种情况下，数据没有成功被删除</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">DELETE FROM &#96;school&#96;.&#96;sc&#96; WHERE (&#96;sno&#96; &#x3D; &#39;202006018&#39;) and (&#96;cno&#96; &#x3D; &#39;C005&#39;);
DELETE FROM &#96;school&#96;.&#96;sc&#96; WHERE (&#96;sno&#96; &#x3D; &#39;202006018&#39;) and (&#96;cno&#96; &#x3D; &#39;C006&#39;);
DELETE FROM &#96;school&#96;.&#96;sc&#96; WHERE (&#96;sno&#96; &#x3D; &#39;202006018&#39;) and (&#96;cno&#96; &#x3D; &#39;C007&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<p>删除表中关于202006018号学生的信息，再次执行删除语句，数据被成功删除</p>
<p>在将学生表在的202006018号学生信息删除后，再次执行</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">INSERT INTO &#96;school&#96;.&#96;sc&#96; (&#96;sno&#96;, &#96;cno&#96;, &#96;grade&#96;) VALUES (&#39;202006018&#39;, &#39;C007&#39;, &#39;90&#39;),(&#39;202006018&#39;, &#39;C006&#39;, &#39;86&#39;),(&#39;202006018&#39;, &#39;C005&#39;, &#39;89&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (&#96;school&#96;.&#96;sc&#96;, CONSTRAINT &#96;fk_sno&#96; FOREIGN KEY (&#96;sno&#96;) REFERENCES &#96;student&#96; (&#96;sno&#96;))<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>因为外键约束的存在，student表中没有该学号存在，DBMS会拒绝插入语句的执行。</p>
<p>数据更新相关</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">update sc set grade&#x3D;grade+10;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>发生MySQL发生报错，错误信息为Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -&gt; SQL Editor and reconnect. </p>
<p>根据报错信息上网搜索相关原因 </p>
<p>报错原因：现在是在mysql的safe-updates模式中，如果where后跟的条件不是主键，就会出现这种错误。</p>
<p>知道问题，解决方法就简单了，要么where后面加上主键的条件，要么就更改模式：</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SET SQL_SAFE_UPDATES &#x3D; 0;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>在语句前面增加 SET SQL_SAFE_UPDATES = 0;语句，增加该语句之后之前的语句可以正常执行了，多次执行之后发现数据超过100，估计是一开始数据库建立的时候没有进行约束。</p>
<h3 id="实验十二：数据库视图的创建与使用"><a href="#实验十二：数据库视图的创建与使用" class="headerlink" title="实验十二：数据库视图的创建与使用"></a>实验十二：数据库视图的创建与使用</h3><p><strong>一、实验目的</strong></p>
<ol>
<li>数据库视图的创建和使用</li>
</ol>
<p><strong>二、实验要求</strong></p>
<ol>
<li><p>了解MySQL数据库视图的创建</p>
</li>
<li><p>使用视图功能及使用</p>
</li>
</ol>
<p><strong>三、实验过程</strong></p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">select * from student where sdept&#x3D;&quot;计算机系&quot; and sage &gt; 20;
-- 为其定义视图，as作为关键字必不可少，select语句作为视图的数据来源
create view cs_view as select * from student where sdept&#x3D;&quot;计算机系&quot; and sage &gt; 20;
-- 查询视图，对视图的查询实际上是执行定义中基本表的查询
select * from cs_view;
-- 一个基本表可定义多个视图
create or replace view cs_view as select * from student where sdept &#x3D;&quot;计算机系&quot;;
-- Select 语句中引用了不可更新的视图Where子句中的子查询，引用from子句中的表；视图与表的区别视图是从一个或多个表（或视图）导出的虚拟表。而表有时也称为基本表——base table。即视图所对应的数据不进行实际存储，数据库中只存储视图的的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关的基本表。在information_schema数据库的tables表中有记录。

select * from cs_view;
-- create view cs_course_view as select sc.sno,sname,sc.cno,  from student,sc,course 
create view sage_sdept_view as select avg(sage) ,sdept from student group by sdept;
drop view sage_sdept_view;
select * from sage_sdept_view;
select avg(sage) ,sdept from student group by sdept;
INSERT INTO &#96;school&#96;.&#96;cs_view&#96; (&#96;sno&#96;, &#96;sname&#96;, &#96;ssex&#96;, &#96;sage&#96;, &#96;sdept&#96;) VALUES (&#39;202006011&#39;, &#39;赵云&#39;, &#39;男&#39;, &#39;20&#39;, &#39;计算机系&#39;);
INSERT INTO &#96;school&#96;.&#96;cs_view&#96; (&#96;sno&#96;, &#96;sname&#96;, &#96;ssex&#96;, &#96;sage&#96;, &#96;sdept&#96;) VALUES (&#39;202006019&#39;, &#39;黄忠&#39;, &#39;男&#39;, &#39;20&#39;, &#39;物理系&#39;);
select * from cs_view;

create or replace view cs_view as select * from student where sdept &#x3D;&quot;计算机系&quot; with check option;
INSERT INTO &#96;school&#96;.&#96;cs_view&#96; (&#96;sno&#96;, &#96;sname&#96;, &#96;ssex&#96;, &#96;sage&#96;, &#96;sdept&#96;) VALUES (&#39;202006019&#39;, &#39;黄忠&#39;, &#39;男&#39;, &#39;20&#39;, &#39;军事系&#39;);
INSERT INTO &#96;school&#96;.&#96;cs_view&#96; (&#96;sno&#96;, &#96;sname&#96;, &#96;ssex&#96;, &#96;sage&#96;, &#96;sdept&#96;) VALUES (&#39;202006011&#39;, &#39;赵云&#39;, &#39;男&#39;, &#39;20&#39;, &#39;计算机系&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>可更新视图要通过视图更新基本表的数据，必须保证视图是可更新的，既可以在insert update等语句中当中使用它们。对于可更新的视图，在视图中的行为和基本表中的行之间具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。</p>
<p>使用视图有以下优点：</p>
<p>1为用户集中数据，简化用户的数据查询、修改、删除和更新，2屏蔽数据库的复杂性3简化用户的权限管理，4便于数据共享，5可以重新组织数据以便输出到其他应用程序中；</p>
<h3 id="实验十三：数据库索引的创建和使用"><a href="#实验十三：数据库索引的创建和使用" class="headerlink" title="实验十三：数据库索引的创建和使用"></a>实验十三：数据库索引的创建和使用</h3><p><strong>一、实验目的</strong></p>
<p><strong>二、实验要求</strong></p>
<ol>
<li><p>了解MySQL数据库索引的创建</p>
</li>
<li><p>使用索引功能及使用</p>
</li>
</ol>
<p><strong>三、实验过程</strong></p>
<p>MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。</p>
<p>索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。</p>
<p>创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。</p>
<p>上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。</p>
<p>建立索引会占用磁盘空间的索引文件。</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">CREATE INDEX indexName ON student (ssex);
-- 创建索引
DROP INDEX indexName ON student; 
-- 删除索引
CREATE TABLE mytable(  
ID INT NOT NULL, username VARCHAR(16) NOT NULL,  INDEX indexName (username)  
 );  
-- 创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
-- 唯一索引 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
-- CREATE UNIQUE INDEX indexName ON table_name(column_name) ;
-- 可以使用 SHOW INDEX 命令来列出表中的相关的索引信息
SHOW INDEX FROM student;
-- 使用ALTER 命令添加和删除索引
-- 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 
# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): #添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
# 该语句指定了索引为 FULLTEXT ，用于全文索引。<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>显示索引信息</p>
<p>你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。</p>
<h3 id="实验十四：存储过程及函数的使用实验"><a href="#实验十四：存储过程及函数的使用实验" class="headerlink" title="实验十四：存储过程及函数的使用实验"></a>实验十四：存储过程及函数的使用实验</h3><p><strong>一、实验目的</strong></p>
<ol>
<li>存储过程及函数的使用实验</li>
</ol>
<p><strong>二、实验要求</strong></p>
<ol>
<li><p>了解MySQL数据库存储过程及函数的异同</p>
</li>
<li><p>使用存储过程实现相关操作</p>
</li>
</ol>
<p><strong>三、实验过程</strong></p>
<p>知识导入：</p>
<p>存储过程，函数和触发器之间的区别：</p>
<ol>
<li><p>存储过程实现的功能要复杂一些，而函数实现的功能针对性比较强。</p>
</li>
<li><p>存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用；</p>
</li>
<li><p>触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。</p>
</li>
</ol>
<p>三者的特点：</p>
<ol>
<li><p>存储过程的特点</p>
<ul>
<li>存储过程只在创建时进行编译，以后执行存储过程都不需要再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。</li>
<li>当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete），可将此复杂操作用存储过程封装起来。</li>
<li>可以在过程中调用另一个存储过程。可以再存储过程中调用函数，可以简化一系列复杂语句。</li>
<li>安全性高，可设定只有某用户才具有对指定存储过程的使用权。</li>
<li>参数有三种（In、Out、In Out），可返回多个参数值。</li>
<li>在Oracle中，若干个有联系的过程可以组合在一起构成程序包。</li>
<li>存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。</li>
</ul>
</li>
<li><p>函数的特点</p>
<ul>
<li>函数只有一种参数（In）,只有一条Return语句，只能返回单一值。</li>
<li>可在SQL语句（DML或Select）中调用函数。由于函数可以返回一个表象，因此它可以在查询语句中位于From关键字的后面。</li>
</ul>
</li>
<li><p>触发器的特点</p>
<ul>
<li>触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行</li>
</ul>
</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">USE school ;
DELIMITER $$
CREATE PROCEDURE insert_student (
  p_sno CHAR(10),
  p_sname VARCHAR (50),
  p_sex VARCHAR (1),
  p_age INT,
  p_dept VARCHAR (20)
  -- 在存储过程中定义的变量跟表中数据没有直接联系
  -- 在数据定义时声明长度可以大于原数据库的数据长度，不会报错，但是在数据输入时弱数据大于数据库要求会造成截断。
) 
BEGIN
  # 声明变量info，以表明插入是否成功。
  DECLARE info VARCHAR (20) DEFAULT &#39;插入成功&#39; ;
  # 异常处理
  DECLARE CONTINUE HANDLER FOR 1062 SET info &#x3D; &#39;插入失败，不能插入重复的数据&#39; ;
  INSERT INTO student 
  VALUES
    (p_sno, p_sname, p_sex, p_age, p_dept) ;
  SELECT 
    info ;
END $$

DELIMITER ;
SET @sno &#x3D; &#39;202006020&#39; ;
SET @sname &#x3D; &#39;魏延&#39; ;
SET @ssex &#x3D; &#39;男&#39; ;
SET @sage &#x3D; 25 ;
SET @sdept &#x3D; &#39;计算机系&#39; ;

CALL insert_student (@sno, @sname, @ssex, @sage, @sdept) ;

SELECT * FROM student ;
-- select @@version 查询版本 系统变量 
-- 查询存储过程 触发器 函数 游标的区别与联系
DELIMITER $$
CREATE PROCEDURE search_sc(
p_sno CHAR(10),
p_cno CHAR(10),
OUT p_grade INT
)
BEGIN
	DECLARE info VARCHAR(30) DEFAULT &#39;查找成功&#39;;
	DECLARE  CONTINUE  HANDLER  FOR  NOT  FOUND  SET  info&#x3D;&#39;查找失败&#39;;
	SELECT  grade  INTO  p_grade  FROM  sc  WHERE  sno&#x3D;p_sno AND cno&#x3D;p_cno;
    SELECT info;
END $$
DELIMITER ;
SET @vsno&#x3D;&#39;202006001&#39;;
SET @vcno&#x3D;&#39;C001&#39;;
CALL search_sc(@vsno,@vcno,@p_grade);
SELECT @p_grade;

# 函数：
SHOW VARIABLES LIKE &#39;%func%&#39;;
SET GLOBAL log_bin_trust_function_creators &#x3D; 1;
-- 第一次使用MySQL的函数功能发生报错1418解决方法如下
-- 1.先查看函数功能是否开启：show variables like &#39;%func%&#39;;
-- 2.开启：SET GLOBAL log_bin_trust_function_creators &#x3D; 1;
-- 3.关闭：SET GLOBAL log_bin_trust_function_creators &#x3D; 0;
USE school;
DELIMITER $$  
DROP FUNCTION IF EXISTS &#96;get_avgscore&#96;;
-- 在MySQL中在已有定于函数之后，我们无法直接在进行操作，我们需要把原有函数删除。
CREATE  FUNCTION  get_avgscore(f_sno CHAR(10))
RETURNS  FLOAT
BEGIN
      RETURN (SELECT  ROUND(AVG(grade), 2)   
                      FROM  SC  WHERE  sno&#x3D;f_sno); 
END $$
DELIMITER ; 
SELECT get_avgscore(&#39;202006001&#39;);<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="实验十五：触发器与游标的学习与使用"><a href="#实验十五：触发器与游标的学习与使用" class="headerlink" title="实验十五：触发器与游标的学习与使用"></a>实验十五：触发器与游标的学习与使用</h3><p><strong>一、实验目的</strong></p>
<ol>
<li><p>学习触发器和游标的使用</p>
</li>
<li><p>掌握MySQL数据库对用户与权限的相关操作的原理及方法</p>
</li>
</ol>
<p><strong>二 、实验要求</strong></p>
<ol>
<li><p>了解MySQL数据库存储过程及函数的异同</p>
</li>
<li><p>使用存储过程实现相关操作</p>
</li>
<li><p>练习了解MySQL数据库用户权限设置</p>
</li>
</ol>
<p><strong>三、实验过程</strong></p>
<p>知识导入：</p>
<p>触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。</p>
<p>代码内容：</p>
<p>触发器与游标相关：</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">delimiter $$
create function get_avgscore(f_sno int)
returns float
begin
return(select round(avg(score),2)
from sc
where sno &#x3D; f_sno);
end $$
delimiter ;
use school;
delimiter $$
create trigger trg_student_delete2
before delete on student
for each row
begin
  delete from sc where sno &#x3D; old.sno;
end $$
delimiter ;
show triggers from school;
select * from student;
delete from student
where sno &#x3D; &#39;202006002&#39;;
select * from sc;
select * from student;
delimiter $$
create function get_avgscore(f_sno char(2))
returns float
begin
	return(select round(avg(score),2)
          from sc where sno&#x3D;f_sno);
          -- return后面括号内部的语句最后一条不加分号，类似于select后面的元素，return后面整体的加分号
end $$
delimiter ;
delimiter $$
create function get_avgscore(f_sno char(3))
returns float
begin
	return(select round(avg(score),2)
			from sc where sno&#x3D;f_sno
      );
end $$
delimiter ;
drop function if exists get_avgscore; -- 删除时函数名后面不加括号
drop function if exists get_avgscore;
drop function if exists get_avgscore;
drop procedure if exists get_avgscore;
drop procedure if exists get_avgscore;
delimiter $$
create procedure sum()
begin
  declare sums3 int;
  declare sums5 int;
  if(sums3 &gt; sums5) then
    	select &#39;s3的总成绩大于s5的总成绩&#39;;
	elseif(sums3 &lt; sums5) then
		select &#39;s3的总成绩小于s5的总成绩&#39;;
	else
       select &#39;s3的总成绩等于s5的总成绩&#39;;
end $$
delimiter ;
use school;
create table if not exists &#96;store&#96; (
    &#39;id&#39; int(11) not null auto_increment,
    &#39;name&#39; varchar(20) not null,
    &#39;count&#39; int(11) not null default &#39;1&#39;,
  primary key (&#39;id&#39;)
) engine&#x3D;innodb default charset&#x3D;latin1 auto_increment&#x3D;7;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>二、用户操作</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql"># 1.查看所有用户信息。
SELECT USER,HOST FROM mysql.user;
# 2.创建用户user1,user3，user4密码自定，主机形式分别为localhost、%和你的主机IP地址，并再次查看用户是否创建成功。。
 CREATE USER &#39;user1&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;1&#39;;
 CREATE USER &#39;user3&#39;@&#39;%&#39; IDENTIFIED BY &#39;3&#39;;
 CREATE USER &#39;user4&#39;@&#39;192.168.82.41&#39; IDENTIFIED BY &#39;4&#39;;
 SELECT USER,HOST FROM mysql.user;
# 3.修改用户user4的名字为user5，并修改其密码（自定），其他不变。
RENAME USER &#39;user4&#39;@&#39;192.168.82.41&#39; TO &#39;user5&#39;@&#39;192.168.82.41&#39;;
ALTER USER &#39;user5&#39;@&#39;192.168.82.41&#39; IDENTIFIED WITH mysql_native_password BY &#39;5&#39;;
RENAME USER &#39;user5&#39;@&#39;localhost&#39; TO &#39;user5&#39;@&#39;192.168.82.41&#39;;  -- 主机名也可以修改，但本题没要求
SELECT USER,HOST FROM mysql.user;
# 4.删除用户user5。
DROP USER user5@192.168.82.41;
SELECT USER,HOST FROM mysql.user;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>– 二、权限操作</p>
<ol>
<li>在root用户的连接下，授予user1创建数据库，创建用户的权限，创建完成后查看用户user1的权限。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT HOST,USER,create_priv,create_user_priv ,grant_priv FROM mysql.user;
GRANT CREATE ON *.* TO user1@localhost;
GRANT CREATE USER ON *.* TO user1@localhost;
SELECT HOST,USER,create_priv,create_user_priv,grant_priv FROM mysql.user; <span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>

<ol start="2">
<li><p>建立user1与MySQL数据库的连接。 </p>
</li>
<li><p>在user1的连接下创建用户user2（其他自定），创建数据库db1,并查看是否创建成功。</p>
</li>
</ol>
<p>在db1数据库中创建t1和t2表，结构和内容自定。测试能否向表t1中插入数据和查询数据，为什么？</p>
<p> – 以下代码在user1的连接下书写并执行：</p>
 <pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">CREATE DATABASE db1;
  CREATE TABLE t1
  ( tid SMALLINT PRIMARY KEY,
   tname VARCHAR(10));
  CREATE TABLE t2
  ( tid2 SMALLINT PRIMARY KEY,
   tname2 VARCHAR(10));
  INSERT INTO t1 VALUES(1,&#39;Tom&#39;);  -- 没有该权限
  SELECT * FROM t1;  -- 没有该权限
  CREATE USER &#39;user2&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;2&#39;;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>   – 以下代码在root的连接下书写并执行，去验证是否有user2用户。</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT USER,HOST FROM mysql.user;  -- 查看是否有user2用户 <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ol start="4">
<li>在root用户的连接下，授予user1对表t1和t2的查询与插入数据的权限，并允许user1传播对t1表和t2表的查询权限。</li>
</ol>
<p>查看user1的相关权限</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
GRANT INSERT ON db1.* TO user1@localhost;
GRANT SELECT ON db1.* TO user1@localhost WITH GRANT OPTION;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;  -- 查看是否授权成功<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>

<ol start="5">
<li>在user1的连接下.测试其是否能查询t1和t2表的内容。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT * FROM t1;
SELECT * FROM t2; <span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<ol start="6">
<li>在root用户的连接下，授予user1可以授权的权限（grant）</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">GRANT GRANT OPTION ON db1.* TO user1@localhost;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ol start="7">
<li>在user1的连接下，将查询t1表的权限授予user2。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">GRANT SELECT ON db1.t1 TO user2@localhost;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ol start="8">
<li>在root用户的连接下，查看user2的权限。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT * FROM mysql.tables_priv;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ol start="9">
<li>建立user2与MySQL数据库的连接.并测试其是否能查询t1表和t2表的内容，对查询结果进行解释。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT * FROM t1;
SELECT * FROM t2;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>回收权限</p>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">#1在root的连接下，回收user1查询db1数据库中所有表的权限授，回收完成后再次查看user1的权限。
REVOKE SELECT ON db1.* FROM user1@localhost;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
#2在user1的连接下，测试其是否还能查询t1和t2表的内容。
SELECT * FROM t1;
SELECT * FROM t2;
#3在user2的连接下，测试其是否还能查询t1表的内容。这说明什么问题？
SELECT * FROM t1;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>三、角色管理</p>
<ol>
<li>在root的连接下，创建角色 role1，主机自定，并在mysql.user中查看创建的角色。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">CREATE ROLE role1;
SELECT USER,HOST FROM mysql.user;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<ol start="2">
<li>在root的连接下，给角色 role1授予更新t1表的权限，并查看角色的权限。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">GRANT UPDATE ON db1.t1 TO role1;
SELECT * FROM mysql.tables_priv;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<ol start="3">
<li>在root的连接下，将角色role1授予用户user2。</li>
</ol>
 <pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">GRANT role1 TO user2@localhost;
SELECT * FROM mysql.tables_priv; -- user2还没有这个权限<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<ol start="4">
<li>在user2的连接下，查看自己拥有的角色。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT CURRENT_ROLE(); -- 结果为空<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ol start="5">
<li>在user2的连接下，启用user2上的role1角色，再次查看user2拥有的角色。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SET ROLE role1;
SELECT CURRENT_ROLE(); -- 结果有了role1角色<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<ol start="6">
<li>在user2的连接下，修改t1表。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">SELECT * FROM t1;
UPDATE t1 SET tname&#x3D;&#39;Marry&#39; WHERE tid&#x3D;1; -- 修改成功
SELECT * FROM t1;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<ol start="7">
<li>在root连接下，撤销user2的role1角色，并再次修改t1表，测试是否成功，为什么？</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">REVOKE role1 FROM user2@localhost;
-- 在user2的连接下，测试是否还能修改t1表
UPDATE t1 SET tname&#x3D;&#39;Marry2&#39; WHERE tid&#x3D;1; -- 不成功<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<ol start="8">
<li>在root的连接下，删除角色role1。</li>
</ol>
<pre class="line-numbers language-mysql" data-language="mysql"><code class="language-mysql">DROP ROLE role1;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
                
            </div>
            <hr/>

            

    <div class="reprint" id="reprint-statement">
        
            <div class="reprint__author">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-user">
                        文章作者:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="/shier_jinghuang/about" rel="external nofollow noreferrer">十二惊惶</a>
                </span>
            </div>
            <div class="reprint__type">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-link">
                        文章链接:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="https://im-so-scared-2.gitee.io/shier_jinghuang/shier_jinghuang/2022/07/06/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AE%9E%E9%AA%8C%E6%8A%A5%E5%91%8A/">https://im-so-scared-2.gitee.io/shier_jinghuang/shier_jinghuang/2022/07/06/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AE%9E%E9%AA%8C%E6%8A%A5%E5%91%8A/</a>
                </span>
            </div>
            <div class="reprint__notice">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-copyright">
                        版权声明:
                    </i>
                </span>
                <span class="reprint-info">
                    本博客所有文章除特別声明外，均采用
                    <a href="https://creativecommons.org/licenses/by/4.0/deed.zh" rel="external nofollow noreferrer" target="_blank">CC BY 4.0</a>
                    许可协议。转载请注明来源
                    <a href="/shier_jinghuang/about" target="_blank">十二惊惶</a>
                    !
                </span>
            </div>
        
    </div>

    <script async defer>
      document.addEventListener("copy", function (e) {
        let toastHTML = '<span>复制成功，请遵循本文的转载规则</span><button class="btn-flat toast-action" onclick="navToReprintStatement()" style="font-size: smaller">查看</a>';
        M.toast({html: toastHTML})
      });

      function navToReprintStatement() {
        $("html, body").animate({scrollTop: $("#reprint-statement").offset().top - 80}, 800);
      }
    </script>



            <div class="tag_share" style="display: block;">
                <div class="post-meta__tag-list" style="display: inline-block;">
                    
                        <div class="article-tag">
                            
                                <a href="/shier_jinghuang/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">
                                    <span class="chip bg-color">数据库</span>
                                </a>
                            
                        </div>
                    
                </div>
                <div class="post_share" style="zoom: 80%; width: fit-content; display: inline-block; float: right; margin: -0.15rem 0;">
                    <link rel="stylesheet" type="text/css" href="/shier_jinghuang/libs/share/css/share.min.css">
<div id="article-share">

    
    <div class="social-share" data-sites="twitter,facebook,google,qq,qzone,wechat,weibo,douban,linkedin" data-wechat-qrcode-helper="<p>微信扫一扫即可分享！</p>"></div>
    <script src="/shier_jinghuang/libs/share/js/social-share.min.js"></script>
    

    

</div>

                </div>
            </div>
            
        </div>
    </div>

    

    

    

    

    

    

    

    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">
                <i class="fas fa-chevron-left"></i>&nbsp;上一篇</div>
            <div class="card">
                <a href="/shier_jinghuang/2022/07/07/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C%E5%AE%9E%E9%AA%8C%E6%8A%A5%E5%91%8A%E6%95%B4%E7%90%86/">
                    <div class="card-image">
                        
                        
                        <img src="/shier_jinghuang/medias/featureimages/22.jpg" class="responsive-img" alt="计算机网络实验报告">
                        
                        <span class="card-title">计算机网络实验报告</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            
                        
                    </div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="far fa-clock fa-fw icon-date"></i>2022-07-07
                        </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/shier_jinghuang/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" class="post-category">
                                    学习笔记
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/shier_jinghuang/tags/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/">
                        <span class="chip bg-color">计算机网络</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">
                下一篇&nbsp;<i class="fas fa-chevron-right"></i>
            </div>
            <div class="card">
                <a href="/shier_jinghuang/2022/07/05/python%E7%9A%84GUI%E7%BC%96%E7%A8%8B%E5%92%8Ctkinter/">
                    <div class="card-image">
                        
                        
                        <img src="/shier_jinghuang/medias/featureimages/8.jpg" class="responsive-img" alt="Python的GUI编程和tkinter，Wxpython">
                        
                        <span class="card-title">Python的GUI编程和tkinter，Wxpython</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            
                        
                    </div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="far fa-clock fa-fw icon-date"></i>2022-07-05
                            </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/shier_jinghuang/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" class="post-category">
                                    学习笔记
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/shier_jinghuang/tags/Python/">
                        <span class="chip bg-color">Python</span>
                    </a>
                    
                    <a href="/shier_jinghuang/tags/tkinter/">
                        <span class="chip bg-color">tkinter</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>

</div>



<!-- 代码块功能依赖 -->
<script type="text/javascript" src="/shier_jinghuang/libs/codeBlock/codeBlockFuction.js"></script>


  <!-- 是否加载使用自带的 prismjs. -->
  <script type="text/javascript" src="/shier_jinghuang/libs/prism/prism.min.js"></script>


<!-- 代码语言 -->

<script type="text/javascript" src="/shier_jinghuang/libs/codeBlock/codeLang.js"></script>


<!-- 代码块复制 -->

<script type="text/javascript" src="/shier_jinghuang/libs/codeBlock/codeCopy.js"></script>


<!-- 代码块收缩 -->

<script type="text/javascript" src="/shier_jinghuang/libs/codeBlock/codeShrink.js"></script>



    </div>
    <div id="toc-aside" class="expanded col l3 hide-on-med-and-down">
        <div class="toc-widget card" style="background-color: white;">
            <div class="toc-title"><i class="far fa-list-alt"></i>&nbsp;&nbsp;目录</div>
            <div id="toc-content"></div>
        </div>
    </div>
</div>

<!-- TOC 悬浮按钮. -->

<div id="floating-toc-btn" class="hide-on-med-and-down">
    <a class="btn-floating btn-large bg-color">
        <i class="fas fa-list-ul"></i>
    </a>
</div>


<script src="/shier_jinghuang/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingsOffset: -($(window).height() * 0.4 - 45),
            collapseDepth: Number('0'),
            headingSelector: 'h2, h3, h4'
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });

        
        /* 修复文章卡片 div 的宽度. */
        let fixPostCardWidth = function (srcId, targetId) {
            let srcDiv = $('#' + srcId);
            if (srcDiv.length === 0) {
                return;
            }

            let w = srcDiv.width();
            if (w >= 450) {
                w = w + 21;
            } else if (w >= 350 && w < 450) {
                w = w + 18;
            } else if (w >= 300 && w < 350) {
                w = w + 16;
            } else {
                w = w + 14;
            }
            $('#' + targetId).width(w);
        };

        // 切换TOC目录展开收缩的相关操作.
        const expandedClass = 'expanded';
        let $tocAside = $('#toc-aside');
        let $mainContent = $('#main-content');
        $('#floating-toc-btn .btn-floating').click(function () {
            if ($tocAside.hasClass(expandedClass)) {
                $tocAside.removeClass(expandedClass).hide();
                $mainContent.removeClass('l9');
            } else {
                $tocAside.addClass(expandedClass).show();
                $mainContent.addClass('l9');
            }
            fixPostCardWidth('artDetail', 'prenext-posts');
        });
        
    });
</script>

    

</main>




    <footer class="page-footer bg-color">
    
        <link rel="stylesheet" href="/shier_jinghuang/libs/aplayer/APlayer.min.css">
<style>
    .aplayer .aplayer-lrc p {
        
        display: none;
        
        font-size: 12px;
        font-weight: 700;
        line-height: 16px !important;
    }

    .aplayer .aplayer-lrc p.aplayer-lrc-current {
        
        display: none;
        
        font-size: 15px;
        color: #42b983;
    }

    
    .aplayer.aplayer-fixed.aplayer-narrow .aplayer-body {
        left: -66px !important;
    }

    .aplayer.aplayer-fixed.aplayer-narrow .aplayer-body:hover {
        left: 0px !important;
    }

    
</style>
<div class="">
    
    <div class="row">
        <meting-js class="col l8 offset-l2 m10 offset-m1 s12"
                   server="netease"
                   type="playlist"
                   id="503838841"
                   fixed='true'
                   autoplay='false'
                   theme='#42b983'
                   loop='all'
                   order='random'
                   preload='auto'
                   volume='0.7'
                   list-folded='true'
        >
        </meting-js>
    </div>
</div>

<script src="/shier_jinghuang/libs/aplayer/APlayer.min.js"></script>
<script src="/shier_jinghuang/libs/aplayer/Meting.min.js"></script>

    

    <div class="container row center-align"
         style="margin-bottom: 0px !important;">
        <div class="col s12 m8 l8 copy-right">
            Copyright&nbsp;&copy;
            
                <span id="year">2019-2023</span>
            
            <a href="/shier_jinghuang/about" target="_blank">十二惊惶</a>
            |&nbsp;Powered by&nbsp;<a href="https://hexo.io/" target="_blank">Hexo</a>
            |&nbsp;Theme&nbsp;<a href="https://github.com/blinkfox/hexo-theme-matery" target="_blank">Matery</a>
            <br>
            
                &nbsp;<i class="fas fa-chart-area"></i>&nbsp;站点总字数:&nbsp;<span
                        class="white-color">289.6k</span>
            
            
            
                
            
            
                <span id="busuanzi_container_site_pv">
                &nbsp;|&nbsp;<i class="far fa-eye"></i>&nbsp;总访问量:&nbsp;
                    <span id="busuanzi_value_site_pv" class="white-color"></span>
            </span>
            
            
                <span id="busuanzi_container_site_uv">
                &nbsp;|&nbsp;<i class="fas fa-users"></i>&nbsp;总访问人数:&nbsp;
                    <span id="busuanzi_value_site_uv" class="white-color"></span>
            </span>
            
            <br>

            <!-- 运行天数提醒. -->
            
            <br>
            
        </div>
        <div class="col s12 m4 l4 social-link social-statis">
    <a href="https://github.com/blinkfox" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
        <i class="fab fa-github"></i>
    </a>



    <a href="mailto:1181062873@qq.com" class="tooltipped" target="_blank" data-tooltip="邮件联系我" data-position="top" data-delay="50">
        <i class="fas fa-envelope-open"></i>
    </a>







    <a href="tencent://AddContact/?fromId=50&fromSubId=1&subcmd=all&uin=1181062873" class="tooltipped" target="_blank" data-tooltip="QQ联系我: 1181062873" data-position="top" data-delay="50">
        <i class="fab fa-qq"></i>
    </a>







    <a href="/shier_jinghuang/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
        <i class="fas fa-rss"></i>
    </a>

</div>
    </div>
</footer>

<div class="progress-bar"></div>


    <!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title"><i class="fas fa-search"></i>&nbsp;&nbsp;搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字"
                   class="search-input">
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script type="text/javascript">
$(function () {
    var searchFunc = function (path, search_id, content_id) {
        'use strict';
        $.ajax({
            url: path,
            dataType: "xml",
            success: function (xmlResponse) {
                // get the contents from search data
                var datas = $("entry", xmlResponse).map(function () {
                    return {
                        title: $("title", this).text(),
                        content: $("content", this).text(),
                        url: $("url", this).text()
                    };
                }).get();
                var $input = document.getElementById(search_id);
                var $resultContent = document.getElementById(content_id);
                $input.addEventListener('input', function () {
                    var str = '<ul class=\"search-result-list\">';
                    var keywords = this.value.trim().toLowerCase().split(/[\s\-]+/);
                    $resultContent.innerHTML = "";
                    if (this.value.trim().length <= 0) {
                        return;
                    }
                    // perform local searching
                    datas.forEach(function (data) {
                        var isMatch = true;
                        var data_title = data.title.trim().toLowerCase();
                        var data_content = data.content.trim().replace(/<[^>]+>/g, "").toLowerCase();
                        var data_url = data.url;
                        data_url = data_url.indexOf('/') === 0 ? data.url : '/' + data_url;
                        var index_title = -1;
                        var index_content = -1;
                        var first_occur = -1;
                        // only match artiles with not empty titles and contents
                        if (data_title !== '' && data_content !== '') {
                            keywords.forEach(function (keyword, i) {
                                index_title = data_title.indexOf(keyword);
                                index_content = data_content.indexOf(keyword);
                                if (index_title < 0 && index_content < 0) {
                                    isMatch = false;
                                } else {
                                    if (index_content < 0) {
                                        index_content = 0;
                                    }
                                    if (i === 0) {
                                        first_occur = index_content;
                                    }
                                }
                            });
                        }
                        // show search results
                        if (isMatch) {
                            str += "<li><a href='" + data_url + "' class='search-result-title'>" + data_title + "</a>";
                            var content = data.content.trim().replace(/<[^>]+>/g, "");
                            if (first_occur >= 0) {
                                // cut out 100 characters
                                var start = first_occur - 20;
                                var end = first_occur + 80;
                                if (start < 0) {
                                    start = 0;
                                }
                                if (start === 0) {
                                    end = 100;
                                }
                                if (end > content.length) {
                                    end = content.length;
                                }
                                var match_content = content.substr(start, end);
                                // highlight all keywords
                                keywords.forEach(function (keyword) {
                                    var regS = new RegExp(keyword, "gi");
                                    match_content = match_content.replace(regS, "<em class=\"search-keyword\">" + keyword + "</em>");
                                });

                                str += "<p class=\"search-result\">" + match_content + "...</p>"
                            }
                            str += "</li>";
                        }
                    });
                    str += "</ul>";
                    $resultContent.innerHTML = str;
                });
            }
        });
    };

    searchFunc('/shier_jinghuang/search.xml', 'searchInput', 'searchResult');
});
</script>

    <!-- 白天和黑夜主题 -->
<div class="stars-con">
    <div id="stars"></div>
    <div id="stars2"></div>
    <div id="stars3"></div>  
</div>

<script>
    function switchNightMode() {
        $('<div class="Cuteen_DarkSky"><div class="Cuteen_DarkPlanet"></div></div>').appendTo($('body')),
        setTimeout(function () {
            $('body').hasClass('DarkMode') 
            ? ($('body').removeClass('DarkMode'), localStorage.setItem('isDark', '0'), $('#sum-moon-icon').removeClass("fa-sun").addClass('fa-moon')) 
            : ($('body').addClass('DarkMode'), localStorage.setItem('isDark', '1'), $('#sum-moon-icon').addClass("fa-sun").removeClass('fa-moon')),
            
            setTimeout(function () {
            $('.Cuteen_DarkSky').fadeOut(1e3, function () {
                $(this).remove()
            })
            }, 2e3)
        })
    }
</script>

    <!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fas fa-arrow-up"></i>
    </a>
</div>


    <script src="/shier_jinghuang/libs/materialize/materialize.min.js"></script>
    <script src="/shier_jinghuang/libs/masonry/masonry.pkgd.min.js"></script>
    <script src="/shier_jinghuang/libs/aos/aos.js"></script>
    <script src="/shier_jinghuang/libs/scrollprogress/scrollProgress.min.js"></script>
    <script src="/shier_jinghuang/libs/lightGallery/js/lightgallery-all.min.js"></script>
    <script src="/shier_jinghuang/js/matery.js"></script>

    

    

    <!-- 雪花特效 -->
    

    <!-- 鼠标星星特效 -->
    

     
        <script src="https://ssl.captcha.qq.com/TCaptcha.js"></script>
        <script src="/shier_jinghuang/libs/others/TencentCaptcha.js"></script>
        <button id="TencentCaptcha" data-appid="xxxxxxxxxx" data-cbfn="callback" type="button" hidden></button>
    

    <!-- Baidu Analytics -->

    <!-- Baidu Push -->

<script>
    (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
            bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
            bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
    })();
</script>

    
    <script src="/shier_jinghuang/libs/others/clicklove.js" async="async"></script>
    
    
    <script async src="/shier_jinghuang/libs/others/busuanzi.pure.mini.js"></script>
    

    

    

    <!--腾讯兔小巢-->
    
    

    

    

    
    <script src="/shier_jinghuang/libs/instantpage/instantpage.js" type="module"></script>
    

</body>

</html>
